﻿using System;
using Comun;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using System.Configuration;


namespace Comun
{
	//Objeto de Conexcion al SQL Server.
	public class ConexionSqlServer :Base
	{
        private String sXMLConfig;
        private bool bDatosAppConfig;
        private SqlConnection sqlConn;
        private SqlTransaction sqlTrans;

		public ConexionSqlServer()
		{
            bDatosAppConfig = true;
            sXMLConfig = "";
		}
        public ConexionSqlServer(string XMLConfig)
		{
            bDatosAppConfig = false;
			sXMLConfig = XMLConfig;
			this.Errores = new Errores();
		}

		private String GetStringConexion(String sClave)
		{
			DataSet ds = new DataSet();
			DataTable Tabla;
						
			ds.ReadXml(sXMLConfig);
	
			Tabla = ds.Tables["Conexion"];

			if (Tabla == null)
				return "";			
			else
				return Tabla.Rows[0][sClave].ToString();
			
		}

		//CONEXION A LA BASE
		public void Conectar()
		{
			try
			{
                String sDataSource="";
                if(bDatosAppConfig)
                    sDataSource =ConfigurationSettings.AppSettings["Conexion"].ToString();
                else
				    sDataSource =GetStringConexion("DataSource");
			
				sqlConn = new SqlConnection();                
				sqlConn.ConnectionString = sDataSource;                
				sqlConn.Open();

			}
			catch (Exception e)
			{

			}
		}

		//COMIENZO DE LA TRANSACCION
		public void ComienzoTransaccion()
		{
			try
			{
				sqlTrans = sqlConn.BeginTransaction();
			}
			catch (Exception e)
			{

			}
		}

		//COMMIT DE LA TRANSACCION
		public void CommitTransaccion()
		{
			try
			{
				sqlTrans.Commit();
				sqlTrans=null;
			}
			catch (Exception e)
			{
				return;
			}			
		}

		//ROLLBACK DE LA TRANSACCION
		public void RollbackTransaccion()
		{
			try
			{
				sqlTrans.Rollback();
				sqlTrans=null;
			}
			catch (Exception e)
			{
				return;
			}
		}

		//DESCONECCION DE LA BASE
		public void Desconectar()
		{
			try
			{
				sqlConn.Close();
				sqlConn=null;
			}
			catch (Exception e)
			{
				return;
			}
		}

		//Ejecuta un Query que no retorna Filas (INSERT,UPDATE,DELETE)
		public int EjecutarQuery(String sQuery)
		{
			try
			{
				int iRes;
				SqlCommand sqlCmd;

				sqlCmd = sqlConn.CreateCommand();

				if (sqlTrans !=null)
					sqlCmd.Transaction =sqlTrans;

				sqlCmd.CommandText = sQuery;

                
				iRes = sqlCmd.ExecuteNonQuery();

				return iRes;
			}
			catch (SqlException e)
			{
				return 0;

			}
			catch (Exception e)
			{
				
				return 0;
			}

		}

		//Ejecuta un Query que retorna Filas </summary>		
		public DataSet EjecutarQuery(String sQuery, String sTabla)
		{
			try
			{
                SqlCommand sqlCmd = new SqlCommand();

				sqlCmd = sqlConn.CreateCommand();

				if (sqlTrans !=null)
					sqlCmd.Transaction =sqlTrans;

				sqlCmd.CommandText = sQuery;
				
				DataSet dsRet = new DataSet();
				SqlDataAdapter sqlAda = new SqlDataAdapter(sqlCmd);

				sqlAda.Fill(dsRet,sTabla);

				return dsRet;

			}
			catch (Exception e)
			{
				return null;
			}

		}

		//Ejecuta un Query que retorna un Valor
		public int EjecutarScalar(String sQuery)
		{
			try
			{
				object rta;
				SqlCommand sqlCmd;
				sqlCmd = sqlConn.CreateCommand();

				if (sqlTrans !=null)
					sqlCmd.Transaction =sqlTrans;

				sqlCmd.CommandText = sQuery;
				rta = sqlCmd.ExecuteScalar();

				if ( rta == null )
					return 0;
				else
					return Convert.ToInt32(rta);
				

			}
			catch (Exception e)
			{
				return 0;
			}

		}

	}
}